import os

from PyQt5.QtCore import QObject
from PyQt5.QtWidgets import QMainWindow, QTableWidgetItem, QMessageBox
from openpyxl.worksheet.worksheet import Worksheet
from Service import Service
import openpyxl

from Frame import Ui_Form


class FrameWrap(Ui_Form):

    def __init__(self, main_window: QMainWindow):
        super().__init__()
        self.setupUi(main_window)
        self.setupEvent()
        self.service = Service(main_window)

    #     设置组件
    def setupUi(self, main_window: QMainWindow):
        super().setupUi(main_window)

    # 设置组件事件
    def setupEvent(self):
        print("设置事件")
        self.selectTZBtn.clicked.connect(self.on_selectTZBtn_clicked)
        self.selectMBbtn.clicked.connect(self.on_selectMBbtn_clicked)
        self.pushButton.clicked.connect(self.on_pushButton_clicked)

    def on_selectTZBtn_clicked(self):
        try:
            print("点击了选择台账按钮")
            tz_path = self.service.selectTz()
            self.lineEdit.setText(tz_path)
            self.set_relect()
        except Exception as e:
            print(e)

    def on_selectMBbtn_clicked(self):
        print("点击了选择模板按钮")
        mb_path = self.service.selectMb()
        self.lineEdit_2.setText(mb_path)

        wb = openpyxl.open(mb_path)
        sheets = wb.sheetnames
        self.textBrowser.setText("模板共有{0}个sheet\n".format(len(sheets)))
        for i in range(len(sheets)):
            self.textBrowser.append("{0}.{1}".format(i + 1, sheets[i]))
        print(sheets)

    def set_relect(self):
        # 获取表头列表
        wb = openpyxl.open(self.lineEdit.text())
        names = wb.sheetnames
        sheet: Worksheet = wb[names[0]]

        self.tableWidget.setRowCount(sheet.max_column)
        self.header = []
        for i in range(1, sheet.max_column + 1):
            value = sheet.cell(2, i).value
            self.header.append(value)
            self.tableWidget.setItem( i -1, 0, QTableWidgetItem(str(value)))

        print("设置完成")
    def on_pushButton_clicked(self):
        print("点击了生成按钮")
        try:
            if ("".__eq__(self.lineEdit.text())
                    or "".__eq__(self.lineEdit_2.text())):
                print("请选择台账和模板1")
                a = QMessageBox.warning(self.pushButton, "警告", "请选择台账和模板", QMessageBox.Yes)

                return

            # 遍历每一行
            map = {}
            for rowNo in range(self.tableWidget.rowCount()):
                #
                if(self.tableWidget.item(rowNo, 0 )==None or
                    self.tableWidget.item(rowNo, 1 )==None or
                    self.tableWidget.item(rowNo, 2 )==None ):
                    continue
                print(rowNo, self.tableWidget.item(rowNo, 0).text())
                colName = self.tableWidget.item(rowNo, 0).text()
                sheetNo = self.tableWidget.item(rowNo, 1).text()
                cellNo = self.tableWidget.item(rowNo, 2).text()
                print(colName, sheetNo, cellNo)
                map[colName]= [sheetNo, cellNo]

            print(map)
        #     转换
            tzWb = openpyxl.open(self.lineEdit.text())


            names = tzWb.sheetnames
            sheet: Worksheet = tzWb[names[0]]


            print("表头",self.header)

            for i, row in enumerate(sheet.rows):
                if(i<=1):continue

                mbWb = openpyxl.open(self.lineEdit_2.text())
                for j, cell in enumerate(row):
                    if self.header[j] in map:
                        if("".__eq__(map[self.header[j]][0]) or "".__eq__(map[self.header[j]][1])):
                            continue
                        sheetNo = map[self.header[j]][0]
                        cellNo = map[self.header[j]][1]
                        mbSheet: Worksheet = mbWb[mbWb.sheetnames[int(sheetNo) - 1]]
                        mbSheet[cellNo] = cell.value
                        print(sheetNo,cellNo,cell.value)
                        path = os.path.expanduser('~') + "\\Desktop\\result"
                        if (not os.path.exists(path)):
                            print("创建文件夹")
                            os.mkdir(path)
                mbWb.save("{0}\\{1}.xlsx".format(path,i+1))
                mbWb.close()
            QMessageBox.warning(self.pushButton, "提示", "生成成功", QMessageBox.Yes)
        except Exception as e:
            print(e)



